using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data.SQLite;
using System.Globalization;
using System.IO;
using ResourceBlender.Core;

namespace ResourceBlender.DAL {
    public enum DatabaseType {
        [EnumBinder.EnumDescription("MySQL")]
        MySQL,

        [EnumBinder.EnumDescription("MS SQL Server")]
        SQLServer,

        SQLite
    }

    /// <summary>
    /// An abstract database connection which can connect to different server types
    /// </summary>
    public class Database {
        private DbConnection conn;
        private DatabaseType type;
        private String tablePrefix;

        /// <summary>
        /// Gets or sets the type of this database instance.
        /// </summary>
        /// <value>The type of the instance.</value>
        public DatabaseType InstanceType {
            get { return type; }
            set { type = value; }
        }

        /// <summary>
        /// Gets a value indicating whether the connection is open.
        /// </summary>
        /// <value><c>true</c> if connected, otherwise <c>false</c>.</value>
        public bool Connected {
            get {
                if(conn != null && conn.State == ConnectionState.Open)
                    return true;
                else
                    return false;
            }
        }

        /// <summary>
        /// Gets a string describing this database connection.
        /// </summary>
        /// <value>The name of the server and database type in use.</value>
        public static string NiceName {
            get {
                try {
                    if(DalConfigurationManager.DBMode == DatabaseType.SQLite) {
                        return string.Format("{0} ({1})", DalConfigurationManager.DBPath, DalConfigurationManager.DBMode);
                    } else {
                        return string.Format("{0}:{1} ({2})", DalConfigurationManager.DBHost, DalConfigurationManager.DBPort, DalConfigurationManager.DBMode);
                    }
                } catch {
                    return I18nUtils.GetString("UIStrings","NoDatabaseConnection");
                }
            }
        }

    /// <summary>
    /// Gets the prefix for ResourceBlender tables.
    /// </summary>
    /// <value>The table prefix.</value>
        public string TablePrefix {
            get { return tablePrefix; }
        }

        /// <summary>
        /// Initializes a new <see cref="Database"/>.
        /// </summary>
        public Database() {
            this.type = DalConfigurationManager.DBMode;
	        this.tablePrefix = DalConfigurationManager.TablePrefix;
	        switch(this.type) {
	            case DatabaseType.MySQL:
	                conn = new MySqlConnection(string.Format(Constants.MySQLConnecionString, DalConfigurationManager.DBHost, DalConfigurationManager.DBPort, DalConfigurationManager.DBDatabase, DalConfigurationManager.DBUser, DalConfigurationManager.DBPassword));
	                break;
	            case DatabaseType.SQLServer:
                    conn = new SqlConnection(string.Format(Constants.SQL2008ConnectionString, DalConfigurationManager.DBHost, DalConfigurationManager.DBPort, DalConfigurationManager.DBDatabase, DalConfigurationManager.DBUser, DalConfigurationManager.DBPassword));
	                break;
                case DatabaseType.SQLite:
                    conn = new SQLiteConnection(string.Format(Constants.SQLiteConnectionString, DalConfigurationManager.DBPath));
                    break;
	        }
		}

        /// <summary>
        /// Opens a connection.
        /// </summary>
        /// <param name="type">The server type.</param>
        /// <param name="connectionString">The connection string.</param>
        public Database(DatabaseType type, string connectionString, string tablePrefix) {
            this.type = type;
            this.tablePrefix = tablePrefix;
            switch(type) {
                case DatabaseType.MySQL:
                    conn = new MySqlConnection(connectionString);
                    break;
                case DatabaseType.SQLServer:
                    conn = new SqlConnection(connectionString);
                    break;
                case DatabaseType.SQLite:
                    conn = new SQLiteConnection(connectionString);
                    break;
            }
            conn.Open();
        }

        /// <summary>
        /// Checks whether the database configuration appears valid. Ie: Is SQLite mode specified without a DB path, or MySQL/SQL Server Mode without a host or port number.
        /// Throws an exception if the configuration is invalid
        /// </summary>
        /// <exception cref="T:System.ArgumentException">If the host, port or database name are missing</exception>
        public void CheckConfig() {
            if(type == DatabaseType.SQLite) {
                string fileName = DalConfigurationManager.DBPath.ToString();
                if(fileName.Length == 0 || !System.IO.File.Exists(fileName)) {
                    throw new System.IO.FileNotFoundException("Error #460: SQLite database not specified or not found");
                }
            } else {
                if(DalConfigurationManager.DBHost.Length == 0 ||  DalConfigurationManager.DBDatabase.Length == 0) {
                    throw new ArgumentException("Error #461: Database credentials not specified");
                }
            }
        }

        /// <summary>
        /// Opens the connection
        /// </summary>
        public void OpenConnection() {
            if(conn.State != ConnectionState.Open) {
                conn.Open();
            }
        }

        /// <summary>
        /// Opens a connection.
        /// </summary>
        /// <param name="type">The server type.</param>
        /// <param name="connectionString">The connection string.</param>
        public void OpenConnection(DatabaseType type, string connectionString) {
            this.type = type;
            switch(type) {
                case DatabaseType.MySQL:
                    conn = new MySqlConnection(connectionString);
                    break;
                case DatabaseType.SQLServer:
                    conn = new SqlConnection(connectionString);
                    break;
                case DatabaseType.SQLite:
                    conn = new SQLiteConnection(connectionString);
                    break;
            }
            conn.Open();
        }

        /// <summary>
        /// Closes the connection.
        /// </summary>
        public void CloseConnection() {
            conn.Close();
        }

        /// <summary>
        /// Checks whether the ResourceBlender tables exist in the database.
        /// </summary>
        /// <returns></returns>
        public bool TablesExist() {
            bool exist = false;
            using(Command cmd = CreateCommand()) {
                DataRow[] rows = null;
                switch(this.type) {
                    case DatabaseType.MySQL:
                        cmd.CommandText = string.Format("SHOW TABLES LIKE '{0}%'", TablePrefix);
                        using(DbDataReader reader = cmd.ExecuteReader()) {
                            exist = reader.HasRows;
                            reader.Close();
                        }
                        break;
                    case DatabaseType.SQLServer:
                        cmd.CommandText = string.Format("IF OBJECT_ID('dbo.{0}applications', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", tablePrefix);
                        exist = Convert.ToBoolean(cmd.ExecuteScalar()); 
                        break;
                    case DatabaseType.SQLite:
                        rows = conn.GetSchema("Tables").Select(string.Format("Table_Name LIKE '{0}%'", tablePrefix));
                        exist = rows != null && rows.Length > 0;
                        break;
                }
            }
            return exist;
        }

        /// <summary>
        /// Creates a database command.
        /// </summary>
        /// <returns></returns>
        public Command CreateCommand() {
            return new Command(type, conn.CreateCommand());
        }

        /// <summary>
        /// Creates a database command and assigns it to a transaction.
        /// </summary>
        /// <returns></returns>
        public Command CreateCommand(DbTransaction trans) {
            return new Command(type, conn.CreateCommand(), trans);
        }

        /// <summary>
        /// Begins a transaction.
        /// </summary>
        /// <returns></returns>
        public DbTransaction BeginTransaction() {
            return conn.BeginTransaction();
        }

        /// <summary>
        /// Creates (if required) and initializes a new database.
        /// </summary>
        /// <param name="dbType">Type of the Database.</param>
        /// <param name="dbPath">The file path (if SQLite), or <c>null</c>.</param>
        /// <param name="attemptCreation"><c>true</c> if the database file should be created first (if SQLite).</param>
        /// <returns></returns>
        public static bool InitializeDatabase(DatabaseType dbType, string dbPath, bool attemptCreation, string dbHost, int dbPort, string dbUser, string dbPass, string dbDatabase, string dbTablePrefix) {
            if(attemptCreation && dbType == DatabaseType.SQLite) {
                try {
                    FileInfo dbFile = new FileInfo(dbPath);
                    if(!dbFile.Directory.Exists) {
                        dbFile.Directory.Create();
                    }
                    if(dbFile.Exists) {
                        throw new ResourceBlenderException(431, "Database file exists");
                    } else {
                        SQLiteConnection.CreateFile(dbPath);
                    }
                } catch(ResourceBlenderException rEx) {
                    throw rEx;
                } catch(Exception ex) {
                    throw new ResourceBlenderException(430, string.Format("Error #430: Could not create file: {0}", ex.Message));
                }
            }

            // connect to database
            Database db = null;
            bool canConnect = false;
            try {
                if(dbType == DatabaseType.SQLite) {
                    db = new Database(DatabaseType.SQLite, Database.GetSQLiteConnectionString(dbPath), dbTablePrefix);
                } else {
                    db = new Database(dbType, Database.GetConnectionString(dbType, dbHost, dbPort, dbUser, dbPass, dbDatabase), dbTablePrefix);
                }
                db.OpenConnection();
                if(db.TablesExist()) {
                    canConnect = true;
                } else {
                    db.CreateTables(true, true, true, true, true, true);
                    canConnect = true;
                }
            } catch {
                throw;
            } finally {
                if(db != null && db.Connected) {
                    try {
                        db.CloseConnection();
                    } catch { }
                }
            }
            return canConnect;
        }

        /// <summary>
        /// Creates the tables needed by ResourceBlender.
        /// </summary>
        /// <param name="applications"><c>true</c> if the applications table should be created.</param>
        /// <param name="elements"><c>true</c> if the elements table should be created.</param>
        /// <param name="resources"><c>true</c> if the resources table should be created.</param>
        /// <param name="resourceComments"><c>true</c> if the resource comments table should be created.</param>
        /// <param name="users"><c>true</c> if the users table should be created.</param>
        /// <param name="templates"><c>true</c> if the templates table should be created.</param>
        public void CreateTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
            if(conn == null) {
            throw new InvalidOperationException(I18nUtils.GetString("UIStrings","ConnectionNotInitialized"));
            } else {
                if(conn.State != ConnectionState.Open) {
                    OpenConnection();
                }
                using(Command cmd = CreateCommand()) {
                    StringBuilder sqlBuffer = new StringBuilder();
                    switch(type) {
                        case DatabaseType.MySQL:
                            if(applications) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}applications` (
                                          `id` varchar(10) NOT NULL default '',
                                          `displayname` varchar(255) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `defaultlanguage` varchar(7) NOT NULL,
                                          `languages` text NOT NULL,
                                          PRIMARY KEY  (`id`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}bundles` (
                                          `id` varchar(10) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `application` varchar(10) NOT NULL,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_bundle` (`name`,`application`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(elements) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}elements` (
                                          `id` int(11) NOT NULL auto_increment,
                                          `applicationid` varchar(10) NOT NULL,
                                          `bundle` varchar(10) NOT NULL,
                                          `elementname` varchar(255) NOT NULL,
                                          `resourceid` int(10) default NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `uniqueelement` (`applicationid`,`elementname`,`bundle`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resources) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}resources` (
                                          `resourceid` int(10) NOT NULL default '0',
                                          `language` varchar(10) NOT NULL,
                                          `datatype` varchar(255),
                                          `mimetype` varchar(255),
                                          `translation` text NOT NULL,
                                          `translatorcomment` text,
                                          PRIMARY KEY  (`resourceid`,`language`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resourceComments) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}resourcecomments` (
                                          `resourceid` int(10) NOT NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`resourceid`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(users) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}users` (
                                          `id` int(255) NOT NULL auto_increment,
                                          `username` varchar(255) NOT NULL,
                                          `password` varchar(255) NOT NULL,
                                          `email` text,
                                          `permissions` int(11) NOT NULL default '0',
                                          `allowedprojects` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_username` (`username`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(templates) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}templates` (
                                              `id` int(5) NOT NULL auto_increment,
                                              `name` varchar(255) NOT NULL,
                                              `filename` varchar(255) NOT NULL,
                                              `filestart` text NOT NULL,
                                              `line` varchar(255) NOT NULL,
                                              `fileend` text NOT NULL,
                                              PRIMARY KEY  (`id`),
                                              UNIQUE KEY `unique_name` (`name`)
                                            ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            sqlBuffer.Append(string.Format(@"INSERT IGNORE INTO `{0}templates` (`name`, `filename`, `filestart`, `line`, `fileend`) VALUES ", TablePrefix))
                                         .Append(@"('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>'),
                                                   ('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php\r\n${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }
                            break;
                        case DatabaseType.SQLServer:
                            if(applications) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}applications](
	                                        [id] [varchar](10) NOT NULL,
	                                        [displayname] [nvarchar](max) NOT NULL,
	                                        [name] [nvarchar](max) NOT NULL,
	                                        [defaultlanguage] [varchar](7) NOT NULL,
	                                        [languages] [varchar](max) NOT NULL
                                        CONSTRAINT [PK_{0}applications] PRIMARY KEY CLUSTERED ([id] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}bundles] (
                                            [id] [varchar](10) NOT NULL,
	                                        [name] [nvarchar](200) NOT NULL,
                                            [application] [varchar](10) NOT NULL,
                                        CONSTRAINT [PK_{0}bundle] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [unique_{0}bundle] UNIQUE NONCLUSTERED ([name] ASC,[application] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(elements) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}elements](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [applicationid] [varchar](10) NOT NULL,
	                                        [bundle] [varchar](10) NOT NULL,
	                                        [elementname] [nvarchar](255) NOT NULL,
	                                        [resourceid] [int] NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}element] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}uniqueelement] UNIQUE NONCLUSTERED ([applicationid] ASC,[bundle] ASC,[elementname] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resources) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}resources](
	                                        [resourceid] [int] NOT NULL,
	                                        [language] [varchar](10) NOT NULL,
                                            [datatype] [nvarchar](255),
                                            [mimetype] [nvarchar](255),
	                                        [translation] [nvarchar](max) NOT NULL,
                                            [translatorcomment] nvarchar(max) NULL,
                                        CONSTRAINT [PK_{0}resources] PRIMARY KEY CLUSTERED ([resourceid] ASC, [language] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resourceComments) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}resourcecomments](
	                                        [resourceid] [int] NOT NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}resourcecomments] PRIMARY KEY CLUSTERED ([resourceid] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(users) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}users](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [username] [nvarchar](100) NOT NULL,
	                                        [password] [nvarchar](100) NOT NULL,
	                                        [email] [nvarchar](100) NULL,
	                                        [permissions] [int] NOT NULL,
	                                        [allowedprojects] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}users] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}uniqueusername] UNIQUE NONCLUSTERED ([username] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(templates) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}templates] (
                                              [id] [int] IDENTITY(10,1) NOT NULL,
                                              [name] [nvarchar](100) NOT NULL,
                                              [filename] [nvarchar](max) NOT NULL,
                                              [filestart] [nvarchar](max) NOT NULL,
                                              [line] [nvarchar](max) NOT NULL,
                                              [fileend] [nvarchar](max) NOT NULL,
                                        CONSTRAINT [PK_{0}templates] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}templatename] UNIQUE NONCLUSTERED ([name] ASC))", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format("INSERT INTO [dbo].[{0}templates] ([name], [filename], [filestart], [line], [fileend]) VALUES ", TablePrefix))
                                         .Append("('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(\"{=Element}\",\"{=Translation}\");', '?>');")
                                         .Append(string.Format("INSERT INTO [dbo].[{0}templates] ([name], [filename], [filestart], [line], [fileend]) VALUES ", TablePrefix))
                                         .Append("('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php ${=Bundle} = array(', '\"{=Element}\" => \"{=Translation}\",', '); ?>');");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }
                            break;
                        case DatabaseType.SQLite:
                            if(applications) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}applications (
                                          id text(10) PRIMARY KEY NOT NULL default '',
                                          displayname text(255) NOT NULL,
                                          name text(255) NOT NULL,
                                          defaultlanguage text(7) NOT NULL,
                                          languages text NOT NULL );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}bundles (
                                          id text(10) PRIMARY KEY NOT NULL,
                                          name text(255) NOT NULL,
                                          application text(10) NOT NULL,
                                          UNIQUE (name,application) );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(elements) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}elements (
                                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                                          applicationid text(10) NOT NULL,
                                          bundle text(10) NOT NULL,
                                          elementname text(255) NOT NULL,
                                          resourceid int(10) default NULL,
                                          comment text,
                                          UNIQUE (applicationid,elementname,bundle) );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resources) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}resources (
                                          resourceid int(10) NOT NULL default '0',
                                          language text(10) NOT NULL,
                                          datatype text(255),
                                          mimetype text(255),
                                          translation text NOT NULL,
                                          translatorcomment text,
                                          PRIMARY KEY (resourceid,language) );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(resourceComments) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}resourcecomments (
                                          resourceid int(10) PRIMARY KEY NOT NULL,
                                          comment text );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(users) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}users (
                                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                                          username text(255) NOT NULL UNIQUE,
                                          password text(255) NOT NULL,
                                          email text,
                                          permissions int(11) NOT NULL default '0',
                                          allowedprojects text );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }

                            if(templates) {
                                sqlBuffer.Append(string.Format(@"CREATE TABLE {0}templates (
                                          id INTEGER PRIMARY KEY AUTOINCREMENT,
                                          name text(255) NOT NULL UNIQUE,
                                          filename text(255) NOT NULL,
                                          filestart text NOT NULL,
                                          line text(255) NOT NULL,
                                          fileend text NOT NULL );", TablePrefix));
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"INSERT INTO {0}templates (name, filename, filestart, line, fileend) VALUES ", TablePrefix))
                                         .Append(@"('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>')");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                                sqlBuffer.Append(string.Format(@"INSERT INTO {0}templates (name, filename, filestart, line, fileend) VALUES ", TablePrefix))
                                         .Append(@"('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php ${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                                cmd.CommandText = sqlBuffer.ToString();
                                sqlBuffer.Remove(0, sqlBuffer.Length);
                                cmd.ExecuteNonQuery();
                            }
                            break;
                    }
                }
            }
        }

        /// <summary>
        /// Drops all tables created by ResourceBlender.
        /// </summary>
        /// <param name="applications"><c>true</c> if the applications table should be dropped.</param>
        /// <param name="elements"><c>true</c> if the elements table should be dropped.</param>
        /// <param name="resources"><c>true</c> if the resources table should be dropped.</param>
        /// <param name="resourceComments"><c>true</c> if the resource comments table should be dropped.</param>
        /// <param name="users"><c>true</c> if the users table should be dropped.</param>
        public void DropTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
            if(conn == null) {
            throw new InvalidOperationException(I18nUtils.GetString("UIStrings","ConnectionNotInitialized"));
            } else {
                if(conn.State != ConnectionState.Open) {
                    OpenConnection();
                }
                using(Command cmd = CreateCommand()) {
                    if(applications) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "applications;";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "bundles;";
                        cmd.ExecuteNonQuery();
                    }
                    if(elements) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "elements";
                        cmd.ExecuteNonQuery();
                    }
                    if(resources) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "resources";
                        cmd.ExecuteNonQuery();
                    }
                    if(resourceComments) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "resourcecomments";
                        cmd.ExecuteNonQuery();
                    }
                    if(templates) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "templates";
                        cmd.ExecuteNonQuery();
                    }
                    if(users) {
                        cmd.CommandText = "DROP TABLE " + tablePrefix + "users";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        /// <summary>
        /// Adds a sample application and some resources to show basic functionality.
        /// </summary>
        public void AddDummyData() {
            if(conn == null) {
            throw new InvalidOperationException(I18nUtils.GetString("UIStrings","ConnectionNotInitialized"));
            } else {
                if(conn.State != ConnectionState.Open) {
                    OpenConnection();
                }
                using(Command cmd = CreateCommand()) {
                    switch(this.InstanceType) {
                        case DatabaseType.MySQL:
                            string[] queries = new string[] {
                                string.Format(@"INSERT IGNORE INTO `{0}applications` (id, displayname, name, defaultlanguage, languages) VALUES ('HJ1556', 'Sample App', 'sampleapp', 'en-US', 'en-US,de-DE,zh-TW');", TablePrefix),
                                string.Format(@"INSERT IGNORE INTO `{0}bundles` (id, name, application) VALUES ('L49181', 'bundle1', 'HJ1556'), ('L49270', 'bundle2', 'HJ1556');", TablePrefix),
                                string.Format(@"INSERT IGNORE INTO `{0}elements` (applicationid, bundle, elementname, resourceid) VALUES 
                                    ('HJ1556','L49181','TranslateMe',15000),('HJ1556','L49181','WinTitle',15002),('HJ1556','L49181','ImAnElement',15004),
                                    ('HJ1556','L49270','AboutResourceBlender',15001),('HJ1556','L49270','CloseButtonText',15003),('HJ1556','L49270','HTMLEncodedLink',15005);", TablePrefix),
                                string.Format(@"INSERT IGNORE INTO `{0}resources` (resourceid, language, translation,translatorcomment) VALUES
                                            (15000,'en-US','Translate Me!',''),
	                                        (15001,'en-US','ResourceBlender allows you to translate literal strings then export them to different formats.',''),
	                                        (15002,'en-US','Translation Example',''),
	                                        (15003,'en-US','Close',''),
	                                        (15004,'en-US','Im a resource','');", TablePrefix)
                            };
                            foreach(string query in queries) {
                                cmd.CommandText = query;
                                cmd.ExecuteNonQuery();
                            }
                            break;
                        case DatabaseType.SQLServer:
                            cmd.CommandText = string.Format(@"IF NOT EXISTS (SELECT * FROM {0}applications WHERE id = 'HJ1556')
                                                BEGIN
                                                INSERT INTO {0}applications (id, displayname, name, defaultlanguage, languages) VALUES ('HJ1556', 'Sample App', 'sampleapp', 'en-US', 'en-US,de-DE,zh-TW');
                                                INSERT INTO {0}bundles (id, name, application) VALUES ('L49181', 'bundle1', 'HJ1556');
                                                INSERT INTO {0}bundles (id, name, application) VALUES ('L49271', 'bundle2', 'HJ1556');

                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','TranslateMe',15000);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','AboutResourceBlender',15001);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','WinTitle',15002);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','CloseButtonText',15003);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','ImAnElement',15004);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','HTMLEncodedLink',15005);

                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15000,'en-US','Translate Me!','');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15001,'en-US','ResourceBlender allows you to translate literal strings then export them to different formats.','');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15002,'en-US','Translation Example','');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15003,'en-US','Close','');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15004,'en-US','Im a resource','');
                                                END", TablePrefix);
                            cmd.ExecuteNonQuery();
                            break;
                        case DatabaseType.SQLite:
                            cmd.CommandText = string.Format(@"INSERT OR IGNORE INTO {0}applications (id, displayname, name, defaultlanguage, languages) VALUES ('HJ1556', 'Sample App', 'sampleapp', 'en-US', 'en-US,de-DE,zh-TW');
                                                            INSERT OR IGNORE INTO {0}bundles (id, name, application) VALUES ('L49181', 'bundle1', 'HJ1556');
                                                            INSERT OR IGNORE INTO {0}bundles (id, name, application) VALUES ('L49271', 'bundle2', 'HJ1556');

                                                            INSERT OR IGNORE INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','TranslateMe',15000);
                                                            INSERT OR IGNORE INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','AboutResourceBlender',15001);
                                                            INSERT OR IGNORE INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','WinTitle',15002);
                                                            INSERT OR IGNORE INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','CloseButtonText',15003);
                                                            INSERT OR IGNORE INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','ImAnElement',15004);
                                                            INSERT OR IGNORE INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','HTMLEncodedLink',15005);

                                                            INSERT OR IGNORE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15000,'en-US','Translate Me!','');
                                                            INSERT OR IGNORE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15001,'en-US','ResourceBlender allows you to translate literal strings then export them to different formats.','');
                                                            INSERT OR IGNORE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15002,'en-US','Translation Example','');
                                                            INSERT OR IGNORE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15003,'en-US','Close','');
                                                            INSERT OR IGNORE INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES (15004,'en-US','Im a resource','');", TablePrefix);
                            cmd.ExecuteNonQuery();
                            break;
                    }
                }
            }
        }

        /// <summary>
        /// Generates an IN clause or an empty string if there are no values.
        /// </summary>
        /// <param name="fieldName">Name of the field.</param>
        /// <param name="values">The values to include.</param>
        /// <param name="useAnd">If <c>true</c>, the clause will be prepended by AND.</param>
        /// <returns>An SQL IN clause</returns>
        /// <example>GenerateInClause("title", new string[] {"ab", "cd"}, true) - <c>AND title IN('ab', 'cd')</c></example>
        /// <example>GenerateInClause("title", new string[] {"ab", "cd"}, false) - <c>title IN('ab', 'cd')</c></example>
        /// <example>GenerateInClause("title", new string[] {}, true) - <c>empty string</c></example>
        public string GenerateInClause(string fieldName, ICollection<string> values, bool useAnd) {
            if(values.Count == 0) {
                return string.Empty;
            } else {
                StringBuilder buffer = new StringBuilder(useAnd ? " AND " : " ");
                buffer.Append(fieldName).Append(" IN('").Append(Utils.Implode(values, "', '", false)).Append("')");
                return buffer.ToString();
            }
        }

        /// <summary>
        /// Generates the crosstab part of a query needed to retrieve columns for the specified languages. Does not include leading or trailing commas.
        /// </summary>
        /// <param name="languages">The language codes for which to create columns.</param>
        /// <returns>The crosstab part of a query needed to retrieve columns for the specified languages. Does not include leading or trailing commas.</returns>
        public string GetCrosstabLanguageSql(IEnumerable<string> languages) {
            StringBuilder sqlBuffer = new StringBuilder();
            string crossTabFormat = null;
            switch(InstanceType) {
                case DatabaseType.MySQL:
                    crossTabFormat = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                    break;
                case DatabaseType.SQLServer:
                case DatabaseType.SQLite:
                    crossTabFormat = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                    break;
            }
            foreach(string language in languages) {
                sqlBuffer.AppendFormat(crossTabFormat, language);
            }
            sqlBuffer.Remove(sqlBuffer.Length - 1, 1);
            return sqlBuffer.ToString();
        }


        /// <summary>
        /// Creates a connection string.
        /// </summary>
        /// <param name="type">The server type.</param>
        /// <param name="host">The host.</param>
        /// <param name="port">The port.</param>
        /// <param name="user">The username.</param>
        /// <param name="pass">The password.</param>
        /// <param name="database">The database name.</param>
        /// <returns></returns>
        public static string GetConnectionString(DatabaseType type, string host, int port, string user, string pass, string database) {
            switch(type) {
                case DatabaseType.MySQL:
                    return string.Format(Constants.MySQLConnecionString, host, port, database, user, pass);
                case DatabaseType.SQLServer:
                    return string.Format(Constants.SQL2008ConnectionString, host, port, database, user, pass);
            }
            return string.Empty;
        }

        /// <summary>
        /// Gets an SQLite connection string for the specified file path.
        /// </summary>
        /// <param name="dbPath">The path of the database file.</param>
        /// <returns>A connection string for the file</returns>
        public static string GetSQLiteConnectionString(string dbPath) {
            return string.Format(Constants.SQLiteConnectionString, dbPath);
        }
    }
    public class DataAdapter {
        private Command cmd;
        private DbDataAdapter adapter;

        public DataAdapter(Command cmd) {
            this.cmd = cmd;
            switch(cmd.DBType) {
                case DatabaseType.MySQL:
                    this.adapter = new MySqlDataAdapter(cmd.InnerCommand as MySqlCommand);
                    break;
                case DatabaseType.SQLServer:
                    this.adapter = new SqlDataAdapter(cmd.InnerCommand as SqlCommand);
                    break;
                case DatabaseType.SQLite:
                    this.adapter = new SQLiteDataAdapter(cmd.InnerCommand as SQLiteCommand);
                    break;
            }
        }

        public void Fill(DataSet ds) {
            adapter.Fill(ds);
        }
        public void Fill(DataTable dt) {
            adapter.Fill(dt);
        }
    }
}